
<cfparam name="showDetail" default="false" />
<cfscript>
	base = new assets.calgrants.com.Base();
</cfscript>
<cfquery name="GetEmail" datasource="WMS">
	select *
	from [2014_CCFC_Email]
	where subject <> 'admin error'
	and from_name <> 'google voice'
	and from_name <> 'delphine henri'
	and from_name <> 'keith evans'
	and from_name <> 'atlassian webinars'
	and from_name <> 'diane mckernon'
	and from_name <> 'adrienne vaughan'
	and from_name <> 'aliza montelongo'
	and from_name <> 'karthy paramananthan'
	and from_name <> 'kathleen finnigan'
	and from_name <> 'jon lee'
	and from_name <> 'kathleen finnigan'
	order by Subject
</cfquery>

<cfset scrubBodyStrings = ArrayNew(1) />
<cfset temp = ArrayAppend(scrubBodyStrings, "@cash4collegeca") />
<cfset temp = ArrayAppend(scrubBodyStrings, "www.facebook.com/cashforcollegecalifornia") />
<cfset temp = ArrayAppend(scrubBodyStrings, "Twitter:") />
<cfset temp = ArrayAppend(scrubBodyStrings, "916-712-0743") />
<cfset temp = ArrayAppend(scrubBodyStrings, "916-464-6410") />
<cfset temp = ArrayAppend(scrubBodyStrings, "Statewide Coordinator") />
<cfset temp = ArrayAppend(scrubBodyStrings, "California Cash for College") />
<cfset temp = ArrayAppend(scrubBodyStrings, "Despina Costopoulos") />
<cfset temp = ArrayAppend(scrubBodyStrings, "________________________________") />
<cfset temp = ArrayAppend(scrubBodyStrings, "-------------------------------------------------------------------------------------------------------------------") />
<cfset temp = ArrayAppend(scrubBodyStrings, "[mailto:ken@syntric.com]") />
<cfset temp = ArrayAppend(scrubBodyStrings, "Despina Dcostopo@csac.ca.gov") />
<cfset temp = ArrayAppend(scrubBodyStrings, "Ken Hobbs - (916) 776-6650") />
<cfset temp = ArrayAppend(scrubBodyStrings, "---------- Forwarded message ----------") />
<cfset temp = ArrayAppend(scrubBodyStrings, "  ") />
<cfset temp = ArrayAppend(scrubBodyStrings, "California Student Aid Commission") />
<cfset temp = ArrayAppend(scrubBodyStrings, "dcostopo@csac.ca.gov <mailto:dcostopo@csac.ca.gov>") />
<cfset temp = ArrayAppend(scrubBodyStrings, "<tel:>") />

<!---
Aliza Montelongo
--->

<cffunction name="scrubSubject" returntype="string">
	<cfargument name="subj" type="string" required="yes">
	<cfreturn Trim(ReplaceNoCase(ReplaceNoCase(ReplaceNoCase(arguments.subj, "fw:", "", "all"), "fwd:", "", "all"), "re:", "", "all"))>
</cffunction>

<cffunction name="scrubBody" returntype="string">
	<cfargument name="str" type="string" required="yes">
	<cfset var ret = str />
	<cfset var = base.stripHTML(ret) />
	<cfloop index="i" from="1" to="#ArrayLen(scrubBodyStrings)#">
		<cfset ret = ReplaceNoCase(ret, scrubBodyStrings[i], "", "all") />
	</cfloop>
	<cfreturn ret />
</cffunction>
<style>
	td {font-size: 0.8em}
	.subjectRow td {background-color: white; font-weight: bold;}
	.subcategoryRow td {background-color: silver; font-weight: bold;}
	.categoryRow td {background-color: black; color: white; font-weight: bold;}
</style>

<cfset qWork = QueryNew("Subject,Body,From_Name,Category,Subcategory,Hours,Body_Length,Keyword") />

<cfset ecmcArray = ArrayNew(1) />
<cfset kresgeArray = ArrayNew(1) />
<cfset miscArray = ArrayNew(1) />

<cfoutput query="GetEmail" group="Subject">
	<cfset workStruct = StructNew() />
	<cfset workStruct.Subject = scrubSubject(Subject)>
	<cfset workStruct.Body = scrubBody(Body)>
	<cfset workStruct.From_Name = From_Name>
	<cfset workStruct.messageCount = 0>
	<cfoutput>
<cfset matchStr = workStruct.Body & " " & workStruct.Subject & workStruct.From_Name />
<cfif FindNoCase("scholarship", matchStr, 1)>
	<cfset  workStruct.Category = "ECMC">
	<cfset  workStruct.Subcategory = "Scholarships">
	<cfset	workStruct.Keyword = "scholarship" />
<cfelseif FindNoCase("hosting", matchStr, 1)>
	<cfset  workStruct.Category = "ECMC">
	<cfset  workStruct.Subcategory = "Hosting">
	<cfset	workStruct.Keyword = "hosting" />
<cfelseif FindNoCase("mailchimp", matchStr, 1)>
	<cfset  workStruct.Category = "ECMC">
	<cfset  workStruct.Subcategory = "MailChimp">
	<cfset	workStruct.Keyword = "mailchimp" />
<cfelseif FindNoCase("matching", matchStr, 1)>
	<cfset  workStruct.Category = "ECMC">
	<cfset  workStruct.Subcategory = "GDS Matching">
	<cfset	workStruct.Keyword = "matching" />
<cfelseif FindNoCase("gds", matchStr, 1)>
	<cfset  workStruct.Category = "ECMC">
	<cfset  workStruct.Subcategory = "GDS Matching">
	<cfset	workStruct.Keyword = "gds" />
<cfelseif FindNoCase("match", matchStr, 1)>
	<cfset  workStruct.Category = "ECMC">
	<cfset  workStruct.Subcategory = "GDS Matching">
	<cfset	workStruct.Keyword = "match" />
<cfelseif FindNoCase("draw", matchStr, 1)>
	<cfset  workStruct.Category = "ECMC">
	<cfset  workStruct.Subcategory = "Scholarships">
	<cfset	workStruct.Keyword = "draw" />
<cfelseif FindNoCase("award", matchStr, 1)>
	<cfset  workStruct.Category = "ECMC">
	<cfset  workStruct.Subcategory = "Scholarships">
	<cfset	workStruct.Keyword = "award" />
<cfelseif FindNoCase("rank", matchStr, 1)>
	<cfset  workStruct.Category = "ECMC">
	<cfset  workStruct.Subcategory = "Scholarships">
	<cfset	workStruct.Keyword = "rank" />
<cfelseif FindNoCase("algorithm", matchStr, 1)>
	<cfset  workStruct.Category = "ECMC">
	<cfset  workStruct.Subcategory = "GDS Matching">
	<cfset	workStruct.Keyword = "algorithm" />
<cfelseif FindNoCase("data entry", matchStr, 1)>
	<cfset  workStruct.Category = "ECMC">
	<cfset  workStruct.Subcategory = "Data Entry">
	<cfset	workStruct.Keyword = "data entry" />
	<cfelseif FindNoCase("paper survey", matchStr, 1)>
	<cfset  workStruct.Category = "ECMC">
	<cfset  workStruct.Subcategory = "Data Entry">
	<cfset	workStruct.Keyword = "paper survey" />
<cfelseif FindNoCase("exit survey", matchStr, 1)>
	<cfset  workStruct.Category = "ECMC">
	<cfset  workStruct.Subcategory = "Wrapper">
	<cfset	workStruct.Keyword = "exit survey" />
<cfelseif FindNoCase("certificate", matchStr, 1)>
	<cfset  workStruct.Category = "ECMC">
	<cfset  workStruct.Subcategory = "Scholarships">
	<cfset	workStruct.Keyword = "certificate" />
<cfelseif FindNoCase("payment", matchStr, 1)>
	<cfset  workStruct.Category = "ECMC">
	<cfset  workStruct.Subcategory = "Payments">
	<cfset	workStruct.Keyword = "payment" />
<cfelseif FindNoCase("carmen", matchStr, 1)>
	<cfset  workStruct.Category = "ECMC">
	<cfset  workStruct.Subcategory = "Payments">
	<cfset	workStruct.Keyword = "carmen" />
<cfelseif FindNoCase("claim", matchStr, 1)>
	<cfset  workStruct.Category = "ECMC">
	<cfset  workStruct.Subcategory = "Scholarships">
	<cfset	workStruct.Keyword = "claim" />
<cfelseif FindNoCase("follow up", matchStr, 1)>
	<cfset  workStruct.Category = "Kresge">
	<cfset  workStruct.Subcategory = "Follow Ups">
	<cfset	workStruct.Keyword = "follow up" />
<cfelseif FindNoCase("fafsa", matchStr, 1)>
	<cfset  workStruct.Category = "Kresge">
	<cfset  workStruct.Subcategory = "FAFSA">
	<cfset	workStruct.Keyword = "fafsa" />
<cfelseif FindNoCase("dashboard", matchStr, 1)>
	<cfset  workStruct.Category = "Kresge">
	<cfset  workStruct.Subcategory = "HDWD">
	<cfset	workStruct.Keyword = "dashboard" />
<cfelseif FindNoCase("how did we do", matchStr, 1)>
	<cfset  workStruct.Category = "Kresge">
	<cfset  workStruct.Subcategory = "HDWD">
	<cfset	workStruct.Keyword = "how did we do" />
<cfelseif FindNoCase("hdwd", matchStr, 1)>
	<cfset  workStruct.Category = "Kresge">
	<cfset  workStruct.Subcategory = "HDWD">
	<cfset	workStruct.Keyword = "hdwd" />
<cfelseif FindNoCase("chart", matchStr, 1)>
	<cfset  workStruct.Category = "Kresge">
	<cfset  workStruct.Subcategory = "HDWD">
	<cfset	workStruct.Keyword = "chart" />
<cfelseif FindNoCase("crosswalk", matchStr, 1)>
	<cfset  workStruct.Category = "Kresge">
	<cfset  workStruct.Subcategory = "HDWD">
	<cfset	workStruct.Keyword = "crosswalk" />
<cfelseif FindNoCase("cross walk", matchStr, 1)>
	<cfset  workStruct.Category = "Kresge">
	<cfset  workStruct.Subcategory = "HDWD">
	<cfset	workStruct.Keyword = "cross walk" />
<cfelseif FindNoCase("pie", matchStr, 1)>
	<cfset  workStruct.Category = "Kresge">
	<cfset  workStruct.Subcategory = "HDWD">
	<cfset	workStruct.Keyword = "pie" />
<cfelseif FindNoCase("bar", matchStr, 1)>
	<cfset  workStruct.Category = "Kresge">
	<cfset  workStruct.Subcategory = "HDWD">
	<cfset	workStruct.Keyword = "bar" />
<cfelseif FindNoCase("site support", matchStr, 1)>
	<cfset  workStruct.Category = "Misc">
	<cfset  workStruct.Subcategory = "Site Support Funds">
	<cfset	workStruct.Keyword = "site support" />
<cfelseif FindNoCase("funds request", matchStr, 1)>
	<cfset  workStruct.Category = "Misc">
	<cfset  workStruct.Subcategory = "Site Support Funds">
	<cfset	workStruct.Keyword = "funds request" />
<cfelseif FindNoCase("epubs", matchStr, 1)>
	<cfset  workStruct.Category = "Misc">
	<cfset  workStruct.Subcategory = "ePubs">
	<cfset	workStruct.Keyword = "epubs" />
<cfelseif FindNoCase("cheryl", matchStr, 1)>
	<cfset  workStruct.Category = "Misc">
	<cfset  workStruct.Subcategory = "ePubs">
	<cfset	workStruct.Keyword = "cheryl" />
<cfelseif FindNoCase("wrapper", matchStr, 1)>
	<cfset  workStruct.Category = "Misc">
	<cfset  workStruct.Subcategory = "Wrapper">
	<cfset	workStruct.Keyword = "wrapper" />
<cfelseif FindNoCase("student account", matchStr, 1)>
	<cfset  workStruct.Category = "Misc">
	<cfset  workStruct.Subcategory = "Scholar Site">
	<cfset	workStruct.Keyword = "student account" />
<cfelseif FindNoCase("scholar", matchStr, 1)>
	<cfset  workStruct.Category = "Misc">
	<cfset  workStruct.Subcategory = "Scholar Site">
	<cfset	workStruct.Keyword = "scholar" />
<cfelseif FindNoCase("order", matchStr, 1)>
	<cfset  workStruct.Category = "Misc">
	<cfset  workStruct.Subcategory = "Orders">
	<cfset	workStruct.Keyword = "order" />
<cfelseif FindNoCase("workshop", matchStr, 1)>
	<cfset  workStruct.Category = "Misc">
	<cfset  workStruct.Subcategory = "Workshops">
	<cfset	workStruct.Keyword = "workshop" />
<cfelseif FindNoCase("regional admin", matchStr, 1)>
	<cfset  workStruct.Category = "Misc">
	<cfset  workStruct.Subcategory = "WMS">
	<cfset	workStruct.Keyword = "regional admin" />
<cfelseif FindNoCase("super user", matchStr, 1)>
	<cfset  workStruct.Category = "Misc">
	<cfset  workStruct.Subcategory = "WMS">
	<cfset	workStruct.Keyword = "super user" />
<cfelseif FindNoCase("export", matchStr, 1)>
	<cfset  workStruct.Category = "Misc">
	<cfset  workStruct.Subcategory = "WMS">
	<cfset	workStruct.Keyword = "export" />
<cfelseif FindNoCase("calgrants.org", matchStr, 1)>
	<cfset  workStruct.Category = "Misc">
	<cfset  workStruct.Subcategory = "calgrants.org">
	<cfset	workStruct.Keyword = "calgrants.org" />
<cfelseif FindNoCase("organizer", matchStr, 1)>
	<cfset  workStruct.Category = "Misc">
	<cfset  workStruct.Subcategory = "WMS">
	<cfset	workStruct.Keyword = "organizer" />
<cfelseif FindNoCase("site contact", matchStr, 1)>
	<cfset  workStruct.Category = "Misc">
	<cfset  workStruct.Subcategory = "WMS">
	<cfset	workStruct.Keyword = "site contact" />
<cfelse>
	<cfset  workStruct.Category = "Misc">
	<cfset  workStruct.Subcategory = "None">
	<cfset	workStruct.Keyword = "" />
</cfif>

		<cfif workStruct.Category eq "ECMC">
			<cfset ArrayAppend(ecmcArray, workStruct)>
		<cfelseif workStruct.Category eq "Kresge">
			<cfset ArrayAppend(kresgeArray, workStruct)>
		<cfelse>
			<cfset ArrayAppend(miscArray, workStruct)>
		</cfif>
		<cfset workStruct.messageCount = workStruct.messageCount + 1 />
		<cfset temp = QueryAddRow(qWork) />
		<cfset temp = QuerySetCell(qWork, "Subject", workStruct.Subject) />
		<cfset temp = QuerySetCell(qWork, "Body", workStruct.Body) />
		<cfset temp = QuerySetCell(qWork, "From_Name", workStruct.From_Name) />
		<cfset temp = QuerySetCell(qWork, "Category", workStruct.Category) />
		<cfset temp = QuerySetCell(qWork, "Subcategory", workStruct.Subcategory) />
		<cfset temp = QuerySetCell(qWork, "Hours", 2.25) />
		<cfset temp = QuerySetCell(qWork, "Body_Length", Len(workStruct.Body)) />
		<cfset temp = QuerySetCell(qWork, "Keyword", workStruct.Keyword) />
	</cfoutput>
</cfoutput>

<cfset summaryHours = QueryNew("Category,Subcategory,Subject,Hours", "varchar,varchar,varchar,double") />

<cfquery name="work" dbtype="query">
	select *
	from qWork
	order by Category, Subcategory, Subject, Keyword, Body_Length
</cfquery>
<table border="1" cellspacing="0">
	<tr>
		<th>Category</th>
		<th>Subcategory</th>
		<!--- <th>Subject</th>
		<th>From</th>
		<th>Body</th> --->
		<th>Hours</th>
		<th>Cost</th>
		<!--- <th>Keyword</th> --->
	</tr>
<cfset totalHours = 0 />
<cfoutput query="work" group="Category">
	<cfset categoryHours = 0 />
	<cfoutput group="Subcategory">
		<cfset subcategoryHours = 0 />
		<cfoutput group="Subject">
			<cfset subjectHours = 0 />
			<cfset messageCount = 0 />
			<cfset messageCost = 0 />
			<cfset fromList = "" />
			<cfset fromCount = 0 />
			<cfset bodLen = 0 />
			<cfoutput>
				<cfif ListFind(fromList, From_Name) eq 0>
					<cfset fromList = ListAppend(fromList, From_Name) />
					<cfset fromCount = fromCount + 1 />
				</cfif>
				<cfif Len(Body) neq bodLen>
					<cfset messageCount = messageCount + 1 />
					<cfset messageHours = messageCount * .75 />
					<cfset messageCost = messageHours * 95 />
					<cfif bodLen eq 0>
						<cfset messageTail = Body />
					<cfelse>
						<cfset messageTail = Right(Body, Len(Body) - bodLen) />
					</cfif>
					<!--- <cfif From_Name neq "Ken Hobbs"> --->
						<!--- <tr>
							<td>#Category#</td>
							<td>#Subcategory#</td>
							<td>#Subject#</td>
							<td>#From_Name#</td>
							<td>#messageTail#</td>
							<td>#messageHours#</td>
							<td>#DollarFormat(messageCost)#</td>
							<td>#Keyword#</td>
						</tr> --->
					<!--- </cfif> --->
					<cfset subjectHours = subjectHours + messageHours />
					<cfset subjectCost = subjectHours * 95 />
					<cfset bodLen = Len(Body) />
				</cfif>
			</cfoutput>
			<!--- <tr class="subjectRow">
				<td colspan="3"></td>
				<td>#fromList#</td>
				<td>#Body#</td>
				<td>#subjectHours#</td>
				<td>#DollarFormat(subjectCost)#</td>
				<td></td>
			</tr> --->
			<!--- <tr>
				<td colspan="5">#Subject# Hours</td>
				<td>#subjectHours#</td>
			</tr> --->
			<cfset subcategoryHours = subcategoryHours + subjectHours />
			<cfset subcategoryCost = subcategoryHours * 95 />
		</cfoutput>
		<tr class="subcategoryRow">
			<td><strong>#Category#</strong></td>
			<td><strong>#Subcategory#</strong></td>
			<!--- <td colspan="4">&nbsp;</td> --->
			<td><strong>#subcategoryHours#</strong></td>
			<td><strong>#DollarFormat(subcategoryCost)#</strong></td>
		</tr>
		<cfset categoryHours = categoryHours + subcategoryHours />
		<cfset categoryCost = categoryHours * 95 />
	</cfoutput>
	<tr class="categoryRow">
		<td><strong>#Category#</strong></td>
		<!--- <td colspan="5">&nbsp;</td> --->
		<td><strong>#categoryHours#</strong></td>
		<td><strong>#DollarFormat(categoryCost)#</strong></td>
	</tr>
	<cfset totalHours = totalHours + categoryHours />
	<cfset totalCost = totalHours * 95 />
</cfoutput>
</table>
<cfoutput>
<h1>Total Cost #DollarFormat(totalCost)#</h1>
	<!--- <tr>
		<td colspan="5">Total Hours</td>
		<td>#totalHours#</td>
	</tr> --->
</cfoutput>
<cfabort>

--------------------------------------------------------------------




<cfoutput>
<h1>ECMC</h1>
<table border="1" cellpadding="4" cellspacing="1">
	<thead> 
		<tr>
			<th>From</th>
			<th>Body</th>
			<th>Bill Type</th>
		</tr>
	</thead>
	<tbody>
		<cfset sub = "">
		<cfloop index="i" from="1" to="#ArrayLen(ecmcArray)#">
			<cfif sub neq ecmcArray[i].Subject>
				<tr>
					<td colspan="3" style="background-color: silver; font-weight: bold;">#ecmcArray[i].Subject# (#ecmcArray[i].messageCount# threads)</td>
				</tr>
				<cfset sub = ecmcArray[i].Subject>
			</cfif>
			<cfif showDetail>
				<tr>
					<td>#ecmcArray[i].From_Name#</td>
					<td><pre>#Body#</pre></td>
					<td>#ecmcArray[i].billType#</td>
				</tr>
			</cfif>
		</cfloop>
	</tbody>
</table>
<h1>ECMC</h1>
<table border="1" cellpadding="4" cellspacing="1">
	<thead> 
		<tr>
			<th>From</th>
			<th>Body</th>
			<th>Bill Type</th>
		</tr>
	</thead>
	<tbody>
		<cfset sub = "">
		<cfloop index="i" from="1" to="#ArrayLen(kresgeArray)#">
			<cfif sub neq kresgeArray[i].Subject>
				<tr>
					<td colspan="3" style="background-color: silver; font-weight: bold;">#kresgeArray[i].Subject# (#kresgeArray[i].messageCount# threads)</td>
				</tr>
				<cfset sub = kresgeArray[i].Subject>
			</cfif>
			<cfif showDetail>
				<tr>
					<td>#kresgeArray[i].From_Name#</td>
					<td>#Left(kresgeArray[i].Body, 200)#</td>
					<td>#kresgeArray[i].billType#</td>
				</tr>
			</cfif>
		</cfloop>
	</tbody>
</table>
<h1>Misc</h1>
<table border="1" cellpadding="4" cellspacing="1">
	<thead> 
		<tr>
			<th>From</th>
			<th>Body</th>
			<th>Bill Type</th>
		</tr>
	</thead>
	<tbody>
		<cfset sub = "">
		<cfloop index="i" from="1" to="#ArrayLen(miscArray)#">
			<cfif sub neq miscArray[i].Subject>
				<tr>
					<td colspan="3" style="background-color: silver; font-weight: bold;">#miscArray[i].Subject# (#miscArray[i].messageCount# threads)</td>
				</tr>
				<cfset sub = miscArray[i].Subject>
			</cfif>
			<cfif showDetail>
				<tr>
					<td>#miscArray[i].From_Name#</td>
					<td>#Left(miscArray[i].Body, 200)#</td>
					<td>#miscArray[i].billType#</td>
				</tr>
			</cfif>
		</cfloop>
	</tbody>
</table>
</cfoutput>



	<!--- <cfquery name="UpdaeRec" datasource="WMS">
		select * 2014_CCFC_Email
		set payment_submitted_date = <cfqueryparam value="#payment_submitted_date#" cfsqltype="cf_sql_date">,
		payment_date = <cfqueryparam value="#payment_date#" cfsqltype="cf_sql_date">,
		payment_type = <cfqueryparam value="#payment_type#" cfsqltype="cf_sql_varchar">,
		payment_amount = <cfqueryparam value="#payment_amount#" cfsqltype="CF_SQL_FLOAT">,
		payment_reference = <cfqueryparam value="#payment_reference#" cfsqltype="cf_sql_varchar">,
		notes = <cfqueryparam value="#notes#" cfsqltype="cf_sql_varchar">
		where payment_id = <cfqueryparam value="#payment_id#" cfsqltype="cf_sql_integer">
	</cfquery> --->
